package exquery.util

import org.apache.log4j.Logger
import org.apache.poi.ss.usermodel.Workbook

import exquery.ClassSampleService
import exquery.ExperimentClassService

import setupx.entity.ClassMetadata
import setupx.entity.ClassSample
import setupx.entity.SampleMetadata
import setupx.entity.Experiment
import setupx.entity.ExperimentClass

import exquery.util.excel.ExcelWriterUtil
import exquery.common.SpeciesUtil
import exquery.common.OrganUtil

import exquery.util.excel.valuebeans.ExcelVO
import exquery.util.excel.valuebeans.SheetVO
import exquery.util.excel.valuebeans.CellVO
import exquery.util.excel.valuebeans.RowVO
import exquery.valuebeans.export.SpeciesVO
import exquery.valuebeans.export.OrganVO

/**
 * Created by IntelliJ IDEA.
 * User: pradeep
 * Date: 4/18/11
 * Time: 1:52 PM
 * Utility class to export experiment into excel.
 */
class ExportExperimentUtil {

    Logger gLogger = Logger.getInstance(ExportExperimentUtil.class)
    private static  ExportExperimentUtil instance = null
    ExperimentClassService classService = new ExperimentClassService()
    ClassSampleService sampleService = new ClassSampleService()

     /**
      * returns the instance
      * @return
      */
    public static  ExportExperimentUtil getInstance() {
      if (instance == null) {
        instance = new  ExportExperimentUtil()
      }

      return instance
    }

    /**
     * to export list of experiments into excel 
     * @param resultList
     * @param params
     * @return org.apache.poi.ss.usermodel.Workbook
     */
    def exportExperiment(def resultList,Map params = [:]){
        gLogger.info("Coming into exportExperiment method of ExportExperimentUtil")
        Workbook workBook

        if (resultList != null && resultList.size() > 0) {
          ExcelVO excelVO = new ExcelVO()

          String excelName = getExcelName(resultList)
          excelVO.setExcelName(excelName)
          def sheets = []
          SheetVO experimentSheet = prepareExperimentSheet(resultList)
          SheetVO classSheet = prepareClassSheet(resultList)
          SheetVO sampleSheet = prepareSampleSheet(resultList)

          sheets.add(experimentSheet)
          sheets.add(classSheet)
          sheets.add(sampleSheet)

          if(params!=null && params.isAll !=null && params.isAll ){
            SheetVO speciesSheet = prepareSpeciesSheet()
            SheetVO organSheet = prepareOrganSheet()
            sheets.add(speciesSheet)
            sheets.add(organSheet)
          }

          excelVO.setSheets(sheets)
          workBook = ExcelWriterUtil.getInstance().generateExcel(excelVO)
        }else{

          ExcelVO excelVO = new ExcelVO()
          SheetVO noRecordSheet = prepareNoDataFoundSheet()
          excelVO.setSheets([noRecordSheet])
          workBook = ExcelWriterUtil.getInstance().generateExcel(excelVO)
        }
        gLogger.info("Out from exportExperiment method of ExportExperimentUtil")
        return workBook
    }

    /**
     * prepare No Data excel sheet
     * @param resultList
     * @return
     */
    private SheetVO prepareNoDataFoundSheet(){
        gLogger.info("Coming into prepareNoDataFoundSheet method of ExportExperimentUtil")

        SheetVO sheetVO = new SheetVO()
        sheetVO.setSheetName("result")
        RowVO headerData = new RowVO()
        def headerCells = []
        headerCells.add(setCell(0,"No Record Found"))
        headerData.setCells(headerCells)
        sheetVO.setHeaderData(headerData)
        gLogger.info("Out from prepareNoDataFoundSheet method of ExportExperimentUtil")
        return sheetVO
    }

    /**
     * prepare excel sheet with all species data
     * @param resultList
     * @return
     */
    private SheetVO prepareSpeciesSheet(){
        gLogger.info("Coming into prepareSpeciesSheet method of ExportExperimentUtil")

        SheetVO sheetVO = new SheetVO()
        sheetVO.setSheetName("species")
        def resultList = SpeciesUtil.getInstance().getUniqueSxSpecies()

        if (resultList != null && resultList.size() > 0) {
          int rowCount=0
          RowVO headerData = new RowVO()
          headerData.setIndex(rowCount++)
          def headerCells = []
          int cellCount=0
          headerCells.add(setCell(cellCount++,"Sr.No."))
          headerCells.add(setCell(cellCount++,"Species Name"))
          headerCells.add(setCell(cellCount++,"NCBI Id"))
          headerCells.add(setCell(cellCount++,"Scientific Name"))
          headerCells.add(setCell(cellCount++,"Synonym(s)"))
          headerCells.add(setCell(cellCount++,"Experiment Count"))
          headerCells.add(setCell(cellCount++,"Classes Count"))
          headerCells.add(setCell(cellCount++,"Samples Count"))
          headerCells.add(setCell(cellCount++,"Organ(s)"))

          headerData.setCells(headerCells)
          sheetVO.setHeaderData(headerData)

          def speciesName = "None"
          def ncbiId = "None"
          def scientificName = "None"
          def synonyms = "None"
          def organs = "None"
          def experimentCount = 0L
          def classesCount = 0L
          def samplesCount = 0L

          RowVO data = new RowVO()
          def rowData = []
          def dataCells = []
          resultList.each {SpeciesVO speciesVO->
            if( speciesVO!=null){

              data = new RowVO()
              data.setIndex(rowCount++)
              dataCells = []

              speciesName = speciesVO.speciesName
              ncbiId = speciesVO.ncbiId
              scientificName = speciesVO.scientificName
              synonyms = speciesVO.synonyms
              synonyms = CommonUtil.getInstance().convertNameListIntoString(speciesVO.synonyms)
              experimentCount = speciesVO.experimentCount
              classesCount = speciesVO.classesCount
              samplesCount = speciesVO.samplesCount
              organs = CommonUtil.getInstance().convertNameListIntoString(speciesVO.organs)

              cellCount=0
              dataCells.add(setCell(cellCount++,data.getIndex()))
              dataCells.add(setCell(cellCount++,speciesName))
              dataCells.add(setCell(cellCount++,ncbiId))
              dataCells.add(setCell(cellCount++,scientificName))
              dataCells.add(setCell(cellCount++,synonyms))
              dataCells.add(setCell(cellCount++,experimentCount))
              dataCells.add(setCell(cellCount++,classesCount))
              dataCells.add(setCell(cellCount++,samplesCount))
              dataCells.add(setCell(cellCount++,organs))
              data.setCells(dataCells)

              rowData.add(data)
            }
          }
          sheetVO.setRowData(rowData)
        }
        gLogger.info("Out from prepareSpeciesSheet method of ExportExperimentUtil")
        return sheetVO
    }

    /**
     * prepare excel sheet with organs data
     * @param resultList
     * @return
     */
    private SheetVO prepareOrganSheet(){
        gLogger.info("Coming into prepareOrganSheet method of ExportExperimentUtil")

        SheetVO sheetVO = new SheetVO()
        sheetVO.setSheetName("organs")
        def resultList = OrganUtil.getInstance().getUniqueSxOrgans()

        if (resultList != null && resultList.size() > 0) {
          int rowCount=0
          RowVO headerData = new RowVO()
          headerData.setIndex(rowCount++)
          def headerCells = []
          int cellCount=0

          headerCells.add(setCell(cellCount++,"Sr.No."))
          headerCells.add(setCell(cellCount++,"Organ Name"))
          headerCells.add(setCell(cellCount++,"Experiment Count"))
          headerCells.add(setCell(cellCount++,"Classes Count"))
          headerCells.add(setCell(cellCount++,"Samples Count"))
          headerCells.add(setCell(cellCount++,"Species"))

          headerData.setCells(headerCells)
          sheetVO.setHeaderData(headerData)

          def organName = "None"
          def experimentCount = 0L
          def classesCount = 0L
          def samplesCount = 0L
          def species = "None"

          RowVO data = new RowVO()
          def rowData = []
          def dataCells = []
          resultList.each {OrganVO organVO->
            if( organVO!=null){

              data = new RowVO()
              data.setIndex(rowCount++)
              dataCells = []

              organName = organVO.organName
              experimentCount = organVO.experimentCount
              classesCount = organVO.classesCount
              samplesCount = organVO.samplesCount
              species = CommonUtil.getInstance().convertNameListIntoString(organVO.species)

              cellCount=0
              dataCells.add(setCell(cellCount++,data.getIndex()))
              dataCells.add(setCell(cellCount++,organName))
              dataCells.add(setCell(cellCount++,experimentCount))
              dataCells.add(setCell(cellCount++,classesCount))
              dataCells.add(setCell(cellCount++,samplesCount))
              dataCells.add(setCell(cellCount++,species))
              data.setCells(dataCells)

              rowData.add(data)
            }
          }
          sheetVO.setRowData(rowData)
        }
        gLogger.info("Out from prepareOrganSheet method of ExportExperimentUtil")
        return sheetVO
    }

    /**
     * prepare excel sheet with experiments data
     * @param resultList
     * @return
     */
    private SheetVO prepareExperimentSheet(def resultList){
        gLogger.info("Coming into prepareExperimentSheet method of ExportExperimentUtil")

        SheetVO sheetVO = new SheetVO()
        sheetVO.setSheetName("experiments")

        if (resultList != null && resultList.size() > 0) {
          int rowCount=0
          RowVO headerData = new RowVO()
          headerData.setIndex(rowCount++)
          def headerCells = []
          int cellCount=0
          headerCells.add(setCell(cellCount++,"Sr.No."))
          headerCells.add(setCell(cellCount++,"Experiment Id"))
          headerCells.add(setCell(cellCount++,"Title"))
          headerCells.add(setCell(cellCount++,"Abstract"))
          headerCells.add(setCell(cellCount++,"Description"))
          headerCells.add(setCell(cellCount++,"Comments"))
          headerCells.add(setCell(cellCount++,"Classes Count"))
          headerCells.add(setCell(cellCount++,"Samples Count"))
          headerData.setCells(headerCells)
          sheetVO.setHeaderData(headerData)

          def id = "None"
          def title = "None"
          def Abstract = "None"
          def description = "None"
          def comment = "None"
          def classesCount
          def samplesCount
          RowVO data = new RowVO()
          def rowData = []
          def dataCells = []
          resultList.each {Experiment experiment->
            if( experiment!=null){

              data = new RowVO()
              data.setIndex(rowCount++)
              dataCells = []

              id = experiment?.id
              title = experiment?.getTitle()
              Abstract = experiment?.getExperiment_abstract()
              description = experiment?.getDescription()
              comment = experiment?.getComments()
              classesCount = classService.getClassesCountByExperimentIds(Long.parseLong("0"+id.toString()))
              samplesCount = sampleService.getSamplesCountByExperimentIds(Long.parseLong("0"+id.toString()))
              cellCount=0
              dataCells.add(setCell(cellCount++,data.getIndex()))
              dataCells.add(setCell(cellCount++,id))
              dataCells.add(setCell(cellCount++,title))
              dataCells.add(setCell(cellCount++,Abstract))
              dataCells.add(setCell(cellCount++,description))
              dataCells.add(setCell(cellCount++,comment))
              dataCells.add(setCell(cellCount++,classesCount))
              dataCells.add(setCell(cellCount++,samplesCount))
              data.setCells(dataCells)

              rowData.add(data)
            }
          }
          sheetVO.setRowData(rowData)
        }
        gLogger.info("Out from prepareExperimentSheet method of ExportExperimentUtil")
        return sheetVO
    }

    /**
     * prepare excel sheet with classes data
     * @param resultList
     * @return
     */
    private SheetVO prepareClassSheet(def resultList){
        gLogger.info("Coming into prepareClassSheet method of ExportExperimentUtil")
        SheetVO sheetVO = new SheetVO()
        sheetVO.setSheetName("classes")

        if (resultList != null && resultList.size() > 0) {
          int rowCount=0
          RowVO headerData = new RowVO()
          headerData.setIndex(rowCount++)
          def headerCells = []
          int cellCount=0
          headerCells.add(setCell(cellCount++,"Sr.No."))
          headerCells.add(setCell(cellCount++,"Class Id"))
          headerCells.add(setCell(cellCount++,"Experiment Id"))
          headerCells.add(setCell(cellCount++,"Species Name"))
          headerCells.add(setCell(cellCount++,"Organ Name"))
          headerCells.add(setCell(cellCount++,"Tissue"))
          headerCells.add(setCell(cellCount++,"Cell Type"))
          headerCells.add(setCell(cellCount++,"Subcellular Celltype"))
          headerCells.add(setCell(cellCount++,"Extraction"))
          headerCells.add(setCell(cellCount++,"Green House"))
          headerCells.add(setCell(cellCount++,"Genotype"))
          headerCells.add(setCell(cellCount++,"Detailed Location"))
          headerCells.add(setCell(cellCount++,"Preparation"))
          headerCells.add(setCell(cellCount++,"Method Name"))
          headerCells.add(setCell(cellCount++,"Samples Count in Class"))
          headerData.setCells(headerCells)
          sheetVO.setHeaderData(headerData)

          def id = "None"
          def extraction = "None"
          def green_house = "None"
          def genotype = "None"

          def detailed_location = "None"
          def preparation = "None"
          def information = "None"

          def method_name = "None"
          def tissue = "None"
          def cell_type = "None"
          def subcellular_celltype = "None"
          def experimentId = "None"

          //def speciesSet
          def speciesName = "None"

          //def organsSet
          def organsName = "None"
          def samplesCount = 0L


          def metaDataCols=[]
          def metaDataDesc=[:]

          resultList.each {Experiment experiment->
              if( experiment!=null){
                 experiment.experimentclassSet.each {ExperimentClass experimentClass->
                     if( experimentClass!=null){
                         def classMetaDataSet = experimentClass?.classMetadataSet
                         if(classMetaDataSet!=null && classMetaDataSet.size()>0){
                             classMetaDataSet.each {ClassMetadata classMetadata->
                                if(classMetadata!=null){
                                   if(!metaDataCols.contains(classMetadata.fieldLogicalId.toString())){
                                      metaDataCols.add(classMetadata.fieldLogicalId.toString())
                                      metaDataDesc.put(classMetadata.fieldLogicalId.toString(),classMetadata.fieldTitle)
                                   }
                                }
                             }
                         }//end if classMetaData
                     }//end if experimentClass
                 }// end experimentclassSet
              }//end if experiment
          }

          gLogger.info("metaDataCols :: ${metaDataCols}")
          gLogger.info("metaDataDesc :: ${metaDataDesc}")

          //Add meta columns header into result sheet

          if(metaDataCols!=null){
              metaDataCols = metaDataCols?.sort{it.toString()}
              metaDataCols.each {meta->
                headerCells.add(setCell(cellCount++,metaDataDesc.get(meta.toString()).toString()))
              }
          }

          RowVO data = new RowVO()
          def rowData = []
          def dataCells = []

          resultList.each {Experiment experiment->
            if( experiment!=null){
                experimentId = experiment.id
                experiment.experimentclassSet.each {ExperimentClass experimentClass->
                if( experimentClass!=null){

                  data = new RowVO()
                  data.setIndex(rowCount++)
                  dataCells = []
                  cellCount = 0
                  id = experimentClass.id
                  extraction = experimentClass.getExtraction()
                  green_house = experimentClass.getGreen_house()
                  genotype = experimentClass.getGenotype()

                  detailed_location = experimentClass.getDetailed_location()
                  preparation = experimentClass.getPreparation()
                  information = experimentClass.getInformation()


                  method_name = experimentClass.getMethod_name()

                  samplesCount = sampleService.getSamplesCountByClassIds(experimentClass.id)

                  /* set the species info : Start */
                  speciesName = CommonUtil.getInstance().prepareSpeciesText(experimentClass?.speciesSet)
                  /* set the species info : End */

                  /* set the organs info : Start */
                  organsName = CommonUtil.getInstance().prepareOrgansText(experimentClass?.organSet)
                  tissue = CommonUtil.getInstance().prepareOrgansTissueText(experimentClass?.organSet)
                  cell_type = CommonUtil.getInstance().prepareOrgansCellTypeText(experimentClass?.organSet)
                  subcellular_celltype = CommonUtil.getInstance().prepareOrgansSubcellularCelltype(experimentClass?.organSet)
                  /* set the organs info : End */

                  dataCells.add(setCell(cellCount++,data.getIndex()))
                  dataCells.add(setCell(cellCount++,id))
                  dataCells.add(setCell(cellCount++,experimentId))
                  dataCells.add(setCell(cellCount++,speciesName))
                  dataCells.add(setCell(cellCount++,organsName))
                  dataCells.add(setCell(cellCount++,tissue))
                  dataCells.add(setCell(cellCount++,cell_type))
                  dataCells.add(setCell(cellCount++,subcellular_celltype))
                  dataCells.add(setCell(cellCount++,extraction))
                  dataCells.add(setCell(cellCount++,green_house))
                  dataCells.add(setCell(cellCount++,genotype))
                  dataCells.add(setCell(cellCount++,detailed_location))
                  dataCells.add(setCell(cellCount++,preparation))
                  dataCells.add(setCell(cellCount++,method_name))
                  dataCells.add(setCell(cellCount++,samplesCount))

                  def classMetaDataSet = experimentClass?.classMetadataSet
                  if(classMetaDataSet!=null && classMetaDataSet.size()>0){
                      if(metaDataCols!=null){
                        metaDataCols?.each {meta->
                            String value=""
                            def tempClassMetaDataSet   = getClassMetadataByLogicalFieldId(classMetaDataSet,meta?.toString())
                            if(tempClassMetaDataSet!=null && tempClassMetaDataSet.size()>0){
                              tempClassMetaDataSet.each {ClassMetadata metaData->
                                  if(metaData!=null && metaData.fieldValue !=null && metaData.fieldValue.toString().trim().length()!=0 ){
                                     if(value.trim().length()==0){
                                         value = HTMLDecoder.getInstance().decode(metaData.fieldValue.toString())
                                         //gLogger.info HTMLDecoder.getInstance().decode(value)
                                     }else{
                                         value += "; ${HTMLDecoder.getInstance().decode(metaData.fieldValue.toString())}"
                                     }
                                  }
                              }
                            }
                            dataCells.add(setCell(cellCount++,value))
                        }
                      }
                  }
                  data.setCells(dataCells)
                  rowData.add(data)
                }
               }
            }
          }
          sheetVO.setRowData(rowData)
        }
        gLogger.info("Out from prepareClassSheet method of ExportExperimentUtil")
        return sheetVO
    }

    /**
     * get class metadata by logical Id  
     * @param classMetaData
     * @param logicalFieldId
     * @return
     */
    private Collection<ClassMetadata> getClassMetadataByLogicalFieldId(def classMetaData,String logicalFieldId){
        def classMetaDataSet=[]
        if(classMetaData!=null && classMetaData.size()>0){
          classMetaData?.each {ClassMetadata metaData->
             if(metaData!=null && metaData.fieldLogicalId.toString().equals(logicalFieldId)){
               classMetaDataSet.add(metaData)
             }
          }
        }
        return classMetaDataSet
    }

    /**
     * prepare excel sheet with samples data
     * @param resultList
     * @return
     */
    private SheetVO prepareSampleSheet(def resultList){
        gLogger.info("Coming into prepareSampleSheet method of ExportExperimentUtil")
        SheetVO sheetVO = new SheetVO()
        sheetVO.setSheetName("samples")

        if (resultList != null && resultList.size() > 0) {
          int rowCount=0
          RowVO headerData = new RowVO()
          headerData.setIndex(rowCount++)
          def headerCells = []
          int cellCount=0
          headerCells.add(setCell(cellCount++,"Sr.No."))
          headerCells.add(setCell(cellCount++,"Sample Id"))
          headerCells.add(setCell(cellCount++,"Class Id"))
          headerCells.add(setCell(cellCount++,"Experiment Id"))
          headerCells.add(setCell(cellCount++,"Label"))
          headerCells.add(setCell(cellCount++,"chromatof Ids"))
          headerCells.add(setCell(cellCount++,"Genotype"))
          headerCells.add(setCell(cellCount++,"SpeciesName"))
          headerCells.add(setCell(cellCount++,"OrganName"))
          headerCells.add(setCell(cellCount++,"Tissue"))

          sheetVO.setHeaderData(headerData)

          def id = "None"
          def class_id = "None"
          def experiment_id = "None"
          def label = "None"
          def genotype = "None"
          def tissue = "None"
          def speciesName= "None"
          def organsName= "None"

          def variations
          String chromatofIds=""

          def rowData = []
          def dataCells = []
          def classList = []
          def sampleList = []

          def metaDataCols=[]
          def metaDataDesc=[:]

          resultList.each {Experiment experiment->
            if(experiment!=null){
              experiment_id = experiment?.id
              classList = experiment?.experimentclassSet

              if(classList!=null && classList.size()>0){
                 classList.each {ExperimentClass clazz->
                   if(clazz!=null){
                       sampleList = clazz?.sampleSet
                       if(sampleList!=null && sampleList.size()>0){
                          sampleList.each {ClassSample sample->
                            def sampleMetaDataSet = sample?.sampleMetadataSet
                            //println "${sample.id}-> sampleMetaDataSet :: ${sampleMetaDataSet}"
                            if(sampleMetaDataSet!=null && sampleMetaDataSet.size()>0){
                                 sampleMetaDataSet.each {SampleMetadata sampleMetadata->
                                    if(sampleMetadata!=null){
                                       if(!metaDataCols.contains(sampleMetadata.fieldLogicalId.toString())){
                                          metaDataCols.add(sampleMetadata.fieldLogicalId.toString())
                                          metaDataDesc.put(sampleMetadata.fieldLogicalId.toString(),sampleMetadata.fieldTitle)
                                       }
                                    }
                                 }
                             }//end if sampleMetaData
                          }// end each sampleList
                       }//end if sampleList
                   }//end if clazz
                 }// end each classList
              }//end if classList
            }//end if experiment
          }//end each resultList

          gLogger.info("metaDataCols :: ${metaDataCols}")
          gLogger.info("metaDataDesc :: ${metaDataDesc}")

          //Add meta columns header into result sheet
          if(metaDataCols!=null){
              metaDataCols = metaDataCols?.sort{it.toString()}
              metaDataCols.each {meta->
                headerCells.add(setCell(cellCount++,metaDataDesc.get(meta.toString()).toString()))
              }
          }
          headerData.setCells(headerCells)

          RowVO data = new RowVO()
          rowData = []
          dataCells = []
          classList = []
          sampleList = []
          resultList.each {Experiment experiment->
            if(experiment!=null){
              experiment_id = experiment?.id
              classList = experiment?.experimentclassSet

              if(classList!=null){
                 classList.each {ExperimentClass clazz->
                   if(clazz!=null){
                      class_id = clazz?.id
                      sampleList = clazz?.sampleSet
                      genotype = clazz?.getGenotype()

                      /* set the species info : Start */
                      speciesName = CommonUtil.getInstance().prepareSpeciesText(clazz?.speciesSet)
                      /* set the species info : End */

                      /* set the organs info : Start */
                      organsName = CommonUtil.getInstance().prepareOrgansText(clazz?.organSet)
                      tissue = CommonUtil.getInstance().prepareOrgansTissueText(clazz?.organSet)
                      /* set the organs info : End */

                      if(sampleList!=null){

                        sampleList.each {ClassSample sample->
                          if(sample!=null){
                            chromatofIds = ""
                            id = sample?.id
                            label = sample?.getLabel()
                            if(sample?.sampleMetadataSet!=null){
                              variations = sample?.sampleMetadataSet
                            }

                            //gLogger.info "variations :: "+variations
                            chromatofIds = CommonUtil.getInstance().prepareChromatofsString(sample?.sampleChromatofSet)

                            data = new RowVO()
                            data.setIndex(rowCount++)
                            dataCells = []
                            cellCount = 0

                            dataCells.add(setCell(cellCount++,data.getIndex()))
                            dataCells.add(setCell(cellCount++,id))
                            dataCells.add(setCell(cellCount++,class_id))
                            dataCells.add(setCell(cellCount++,experiment_id))
                            dataCells.add(setCell(cellCount++,label))
                            dataCells.add(setCell(cellCount++,chromatofIds))
                            dataCells.add(setCell(cellCount++,genotype))
                            dataCells.add(setCell(cellCount++,speciesName))
                            dataCells.add(setCell(cellCount++,organsName))
                            dataCells.add(setCell(cellCount++,tissue))

                            def sampleMetaDataSet = sample?.sampleMetadataSet
                            if(sampleMetaDataSet!=null && sampleMetaDataSet.size()>0){
                              if(metaDataCols!=null){
                                metaDataCols?.each {meta->
                                    String value=""
                                    def tempSampleMetaDataSet   = getSampleMetadataByLogicalFieldId(sampleMetaDataSet,meta?.toString())
                                    if(tempSampleMetaDataSet!=null && tempSampleMetaDataSet.size()>0){
                                      tempSampleMetaDataSet.each {SampleMetadata metaData->
                                          if(metaData!=null && metaData.fieldValue !=null && metaData.fieldValue.toString().trim().length()!=0 ){
                                             if(value.trim().length()==0){
                                                 value = HTMLDecoder.getInstance().decode(metaData.fieldValue.toString())
                                                 //gLogger.info HTMLDecoder.getInstance().decode(value)
                                             }else{
                                                 value += "; ${HTMLDecoder.getInstance().decode(metaData.fieldValue.toString())}"
                                             }
                                          }//end if metaData
                                      }//end each tempSampleMetaDataSet
                                    }//end if tempSampleMetaDataSet

                                    dataCells.add(setCell(cellCount++,value))

                                }//end each metaDataCols
                              }//end if metaDataCols
                            }//end if sampleMetaDataSet

                            data.setCells(dataCells)
                            rowData.add(data)
                          }
                        }
                      }// end sampleList if
                   }
                 }
              }// end classList if
            }
          }
          sheetVO.setRowData(rowData)
        }
        gLogger.info("Out from prepareSampleSheet method of ExportExperimentUtil")
        return sheetVO
    }

    /**
     * get sample metadata by logical Id
     * @param sampleMetaData
     * @param logicalFieldId
     * @return
     */
    private Collection<SampleMetadata> getSampleMetadataByLogicalFieldId(def sampleMetaData,String logicalFieldId){
        def sampleMetaDataSet=[]
        if(sampleMetaData!=null && sampleMetaData.size()>0){
          sampleMetaData?.each {SampleMetadata metaData->
             if(metaData!=null && metaData.fieldLogicalId.toString().equals(logicalFieldId)){
               sampleMetaDataSet.add(metaData)
             }
          }
        }
        return sampleMetaDataSet
    }

    /**
     * set the cell value been 
     * @param index
     * @param value
     * @return
     */  
    private CellVO setCell(int index,def value=null){
        CellVO cell = new CellVO()

        if(index>-1){
           cell.setIndex(index)
        }else{
            cell.setIndex((short) 0)
        }

        if(value!=null){
          cell.setValue(value)
        }

        return cell
    }

    /**
     * to generate dynamic excel name  
     * @param resultList
     * @return
     */
    private String getExcelName(def resultList){
        String excelName = "";
        if(resultList!=null && resultList.size()>0){
            if(resultList.size == 1){
                excelName = "Experiment_${resultList?.get(0)?.id}"
            }else if(resultList.size()<16) {
               resultList.eachWithIndex {Experiment experiment,index->
                if( experiment!=null){
                   if(excelName.trim().length()==0){
                       excelName = "Experiment_${experiment?.id}"
                   }else{
                       excelName += "_${experiment?.id}"
                   }
                }
               }
            }else if(resultList.size()>16) {
              excelName = "Export"
            }
        }else{
           excelName = "Export"
        }
        return excelName
    }

}
